package com.dd.cloud.common.utils;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import cn.afterturn.easypoi.excel.entity.result.ExcelVerifyHandlerResult;
import cn.afterturn.easypoi.handler.inter.IExcelVerifyHandler;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.NoSuchElementException;

/**
 * cn.afterturn 导入导出Excel工具类
 */
public class ExtUtils implements IExcelVerifyHandler<Object> {

    /**
     * excel 导出
     *
     * @param list           数据
     * @param title          标题
     * @param sheetName      sheet名称
     * @param pojoClass      pojo类型
     * @param fileName       文件名称
     * @param isCreateHeader 是否创建表头
     * @param response
     */
    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, boolean isCreateHeader, HttpServletResponse response) throws IOException {
        ExportParams exportParams = new ExportParams(title, sheetName, ExcelType.XSSF);
        exportParams.setCreateHeadRows(isCreateHeader);
        defaultExport(list, pojoClass, fileName, response, exportParams);
    }

    /**
     * excel 导出
     *
     * @param list      数据
     * @param title     标题
     * @param sheetName sheet名称
     * @param pojoClass pojo类型
     * @param fileName  文件名称
     * @param response
     */
    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass, String fileName, HttpServletResponse response) throws IOException {
        defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName, ExcelType.XSSF));
    }

    /**
     * excel 导出
     *
     * @param list         数据
     * @param pojoClass    pojo类型
     * @param fileName     文件名称
     * @param response
     * @param exportParams 导出参数
     */
    public static void exportExcel(List<?> list, Class<?> pojoClass, String fileName, ExportParams exportParams, HttpServletResponse response) throws IOException {
        defaultExport(list, pojoClass, fileName, response, exportParams);
    }

    /**
     * excel 导出
     *
     * @param list     数据
     * @param fileName 文件名称
     * @param response
     */
    public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response) throws IOException {
        defaultExport(list, fileName, response);
    }

    /**
     * 默认的 excel 导出
     *
     * @param list         数据
     * @param pojoClass    pojo类型
     * @param fileName     文件名称
     * @param response
     * @param exportParams 导出参数
     */
    private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams) throws IOException {
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, pojoClass, list);
        downLoadExcel(fileName, response, workbook);
    }

    /**
     * 默认的 excel 导出
     *
     * @param list     数据
     * @param fileName 文件名称
     * @param response
     */
    private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) throws IOException {
        Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
        downLoadExcel(fileName, response, workbook);
    }

    /**
     * 下载
     *
     * @param fileName 文件名称
     * @param response
     * @param workbook excel数据
     */
    private static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) throws IOException {
        try {
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName + "." + ExcelTypeEnum.XLSX.getValue(), "UTF-8"));
            workbook.write(response.getOutputStream());
        } catch (Exception e) {
            throw new IOException(e.getMessage());
        }
    }

    /**
     * excel 导入
     *
     * @param filePath   excel文件路径
     * @param titleRows  标题行
     * @param headerRows 表头行
     * @param pojoClass  pojo类型
     * @param <T>
     * @return
     */
    public static <T> List<T> importExcel(String filePath, Integer titleRows, Integer headerRows, Class<T> pojoClass) throws IOException {
        if (StringUtils.isBlank(filePath)) {
            return null;
        }


        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        params.setNeedSave(true);
        params.setSaveUrl("/excel/");
        try {
            return ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
        } catch (NoSuchElementException e) {
            throw new IOException("模板不能为空");
        } catch (Exception e) {
            throw new IOException(e.getMessage());
        }
    }

    /**
     * excel 导入
     *
     * @param file      excel文件
     * @param pojoClass pojo类型
     * @param <T>
     * @return
     */
    public static <T> List<T> importExcel(MultipartFile file, Class<T> pojoClass) throws IOException {
        return importExcel(file, 1, 1, pojoClass);
    }

    /**
     * excel 导入
     *
     * @param file       excel文件
     * @param titleRows  标题行
     * @param headerRows 表头行
     * @param pojoClass  pojo类型
     * @param <T>
     * @return
     */
    public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass) throws IOException {
        return importExcel(file, titleRows, headerRows, false, pojoClass, false);
    }

    /**
     * excel 导入
     *
     * @param file       excel文件
     * @param titleRows  标题行
     * @param headerRows 表头行
     * @param pojoClass  pojo类型
     * @param <T>
     * @param dis        是否需要拆解单元格
     * @return
     */
    public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass, Boolean dis) throws IOException {
        return importExcel(file, titleRows, headerRows, false, pojoClass, dis);
    }

    /**
     * excel 导入
     *
     * @param file       上传的文件
     * @param titleRows  标题行
     * @param headerRows 表头行
     * @param needVerfiy 是否检验excel内容
     * @param pojoClass  pojo类型
     * @param dis        是否需要拆解单元格
     * @param <T>
     * @return
     */
    public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, boolean needVerfiy, Class<T> pojoClass, Boolean dis) throws IOException {
        if (file == null) {
            return null;
        }
        try {
            String suffix = null;
            String originalFilename = file.getOriginalFilename();
            if (originalFilename != null && originalFilename.contains(".")) {
                suffix = originalFilename.substring(originalFilename.lastIndexOf(".") + 1);
            }

            return importExcel(file.getInputStream(), titleRows, headerRows, needVerfiy, pojoClass, dis , suffix);
        } catch (Exception e) {
            throw new IOException(e.getMessage());
        }
    }

    /**
     * excel 导入
     *
     * @param inputStream 文件输入流
     * @param titleRows   标题行
     * @param headerRows  表头行
     * @param needVerfiy  是否检验excel内容
     * @param pojoClass   pojo类型
     * @param dis   是否需要拆解单元格
     * @param <T>
     * @return
     */
    public static <T> List<T> importExcel(InputStream inputStream, Integer titleRows, Integer headerRows, boolean needVerfiy, Class<T> pojoClass, boolean dis, String suffix) throws IOException {
        if (inputStream == null) {
            return null;
        }
        if (dis){
            //    1.获取workBook
            Workbook workbook = getWorkBook(inputStream, suffix);
            //    2.获取sheet页
            Sheet sheetAt0 = workbook.getSheetAt(0);
            //    3.拆解合并单元格 拆解后就相当于是普通的excel导入了
            decomposeMergedCell(sheetAt0);
            ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
            workbook.write(outputStream);
            ByteArrayInputStream is = new ByteArrayInputStream(
                    outputStream.toByteArray());
            inputStream = is;
        }


        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
//        params.setSaveUrl("/excel/");
//        params.setNeedSave(true);
        params.setNeedVerify(needVerfiy);
        //自定义的转Object验证,如果为空行, 则忽略该行
        params.setVerifyHandler(new ExtUtils());
        try {
            return ExcelImportUtil.importExcel(inputStream, pojoClass, params);
        } catch (NoSuchElementException e) {
            throw new IOException("excel文件不能为空");
        } catch (Exception e) {
            e.printStackTrace();
            throw new IOException(e.getMessage());
        }
    }

    public static String setCellValue(Cell cellTmp, Cell cell) {

        if (cellTmp == null) {
            return "";
        }

        if (cellTmp.getCellType() == CellType.STRING) {
            cell.setCellValue(cellTmp.getStringCellValue());
            return cellTmp.getStringCellValue();
        } else if (cellTmp.getCellType() == CellType.BOOLEAN) {
            cell.setCellValue(cellTmp.getBooleanCellValue());
            return String.valueOf(cellTmp.getBooleanCellValue());
        } else if (cellTmp.getCellType() == CellType.FORMULA) {
            cell.setCellValue(cellTmp.getCellFormula());
            return cellTmp.getCellFormula();

        } else if (cellTmp.getCellType() == CellType.NUMERIC) {
            cell.setCellValue(cellTmp.getNumericCellValue());
            return String.valueOf(cellTmp.getNumericCellValue());

        }

        return "";

    }


    /**
     * 判断是否是合并单元格, 如果是, 则拆解
     */
    public static void decomposeMergedCell(Sheet sheetAt0) {
        //获取合并单元格信息的hashmap
        Map<String, Integer[]> mergedRegionMap = getMergedRegionMap(sheetAt0);
        //拿到excel的最后一行的索引
        int lastRowNum = sheetAt0.getLastRowNum();
        //从excel的第二行索行开始，遍历到最后一行（第一行是标题，直接跳过不读取）
        for (int i = 0; i <= lastRowNum; i++) {
            //拿到excel的行对象
            Row row = sheetAt0.getRow(i);
            //获取excel的行中有多个列
            int cellNum = row.getLastCellNum();
            //对每行进行列遍历，即一列一列的进行解析
            for (int j = 0; j < cellNum; j++) {
                //拿到了excel的列对象
                Cell cell = row.getCell(j);
                //将列对象的行号和列号+下划线组成key去hashmap中查询，不为空说明当前的cell是合并单元列
                Integer[] firstRowNumberAndCellNumber = mergedRegionMap.get(i + "_" + j);
                //如果是合并单元列，就取合并单元格的首行和首列所在位置读数据，否则就是直接读数据
                if (firstRowNumberAndCellNumber != null) {
                    Row rowTmp = sheetAt0.getRow(firstRowNumberAndCellNumber[0]);
                    Cell cellTmp = rowTmp.getCell(firstRowNumberAndCellNumber[1]);
                    setCellValue(cellTmp, cell);
                }
            }
        }
    }

    //将存在合并单元格的列记录入put进hashmap并返回
    public static Map<String, Integer[]> getMergedRegionMap(Sheet sheet) {

        Map<String, Integer[]> result = new HashMap<String, Integer[]>();

        //获取excel中的所有合并单元格信息
        int sheetMergeCount = sheet.getNumMergedRegions();

        //遍历处理
        for (int i = 0; i < sheetMergeCount; i++) {

            //拿到每个合并单元格，开始行，结束行，开始列，结束列
            CellRangeAddress range = sheet.getMergedRegion(i);
            int firstColumn = range.getFirstColumn();
            int lastColumn = range.getLastColumn();
            int firstRow = range.getFirstRow();
            int lastRow = range.getLastRow();

            //构造一个开始行和开始列组成的数组
            Integer[] firstRowNumberAndCellNumber = new Integer[]{firstRow, firstColumn};

            //遍历，将单元格中的所有行和所有列处理成由行号和下划线和列号组成的key，然后放在hashmap中
            for (int currentRowNumber = firstRow; currentRowNumber <= lastRow; currentRowNumber++) {

                for (int currentCellNumber = firstColumn; currentCellNumber <= lastColumn; currentCellNumber++) {
                    result.put(currentRowNumber + "_" + currentCellNumber, firstRowNumberAndCellNumber);
                }
            }
        }
        return result;
    }


    public static Workbook getWorkBook(InputStream is, String suffix) throws IOException {
        //这样写excel能兼容03和07
        Workbook hssfWorkbook = null;

        if (suffix == null){
            try {
                hssfWorkbook = new HSSFWorkbook(is);
            } catch (Exception ex) {
                hssfWorkbook = new XSSFWorkbook(is);
            }
            return hssfWorkbook;
        }
        if (suffix.equals(ExcelTypeEnum.XLS.value)){
            try {
                hssfWorkbook = new HSSFWorkbook(is);
            } catch (Exception ex) {
                //String xlsPath = HtmlToExc.toExcel(filehtml.getPath(), "roster",path);
                ex.printStackTrace();

            }
        }

        if (suffix.equals(ExcelTypeEnum.XLSX.value)){
            try {
                hssfWorkbook = new XSSFWorkbook(is);
            } catch (Exception ex) {
                ex.printStackTrace();

            }
        }
        return hssfWorkbook;
    }

    /**
     * excel 导入
     *
     * @param inputStream 文件输入流
     * @param titleRows   标题行
     * @param headerRows  表头行
     * @param pojoClass   pojo类型
     * @param <T>
     * @return
     * @Param 多表格导入
     */
    public static <T> List<T> importExcel(InputStream inputStream, Integer sheet, Integer titleRows, Integer headerRows, Class<T> pojoClass) throws IOException {
        if (inputStream == null) {
            return null;
        }
        ImportParams params = new ImportParams();
        params.setStartSheetIndex(sheet);
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        params.setNeedVerify(false);
        //自定义的转Object验证,如果为空行, 则忽略该行
        params.setVerifyHandler(new ExtUtils());
        try {
            return ExcelImportUtil.importExcel(inputStream, pojoClass, params);
        } catch (NoSuchElementException e) {
            throw new IOException("excel文件不能为空");
        } catch (Exception e) {
            throw new IOException(e.getMessage());
        }
    }

    /**
     * 自定义的验证规则
     *
     * @param obj
     * @return
     */
    @Override
    public ExcelVerifyHandlerResult verifyHandler(Object obj) {
        ExcelVerifyHandlerResult result = new ExcelVerifyHandlerResult(true);
        if (com.baomidou.mybatisplus.core.toolkit.ObjectUtils.isNotNull(obj)) {
            //判断对象属性是否全部为空
            boolean b = ObjectUtils.objCheckIsNull(obj);
            result.setSuccess(!b);
        }
        return result;
    }

    /**
     * Excel 类型枚举
     */
    enum ExcelTypeEnum {
        XLS("xls"), XLSX("xlsx");
        private String value;

        ExcelTypeEnum(String value) {
            this.value = value;
        }

        public String getValue() {
            return value;
        }

        public void setValue(String value) {
            this.value = value;
        }
    }
}
